package club.smartdot.example;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Resolve {

	public static void main(String[] args) {
		// File file = new File("D://dd.xlsx");
		 File file = new File(Resolve.class.getClass().getResource("/example.xlsx").getPath());
		try {
			Workbook workbook = null;
			InputStream inputStream = new FileInputStream(file);
			if (file.getName().endsWith("xls")) {
				// Excel 2003
				workbook = new HSSFWorkbook(inputStream);
			} else if (file.getName().endsWith("xlsx")) {
				// Excel 2007/2010
				workbook = new XSSFWorkbook(inputStream);
			}
			inputStream.close();
			// 工作表对象
			Sheet sheet = workbook.getSheetAt(0);
			// 总行数
			int rowLength = sheet.getLastRowNum() + 1;
			// 工作表的列
			Row row = sheet.getRow(0);
			// 总列数
			int colLength = row.getLastCellNum();
			// 得到指定的单元格
			Cell cell = row.getCell(0);
			// 得到单元格样式
			// CellStyle cellStyle = cell.getCellStyle();
			System.out.println("行数：" + rowLength + ",列数：" + colLength);
			for (int i = 0; i < rowLength; i++) {
				row = sheet.getRow(i);
				for (int j = 0; j < colLength; j++) {
					cell = row.getCell(j);
					// Excel数据Cell有不同的类型，当我们试图从一个数字类型的Cell读取出一个字符串时就有可能报异常：
					// Cannot get a STRING value from a NUMERIC cell
					// 将所有的需要读的Cell表格设置为String格式
					if (cell != null) {
						// cell.setCellType(CellType.STRING);
					} else {
						// 没有cell将创建一个
						cell = row.createCell(1, CellType.STRING);
					}
					// 对Excel进行修改
					if (i > 0 && j == 1) {
						cell.setCellValue(String.valueOf(new Random().nextInt(10)));
					}
					System.out.print(cell.getStringCellValue() + "\t");
				}
				System.out.println();
			}

			// 将修改好的数据保存
			OutputStream outputStream = new FileOutputStream(file);
			workbook.write(outputStream);
			workbook.close();
			outputStream.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}



